package com.pzhu.dao.impl;

import com.pzhu.dao.BookDao;
import com.pzhu.pojo.Book;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class BookDaoImpl implements BookDao {

    private final String selectBookSql = "select * from tb_book_info b,tb_student u where b.id=? and (b.update=u.id);";
    private final String selectAllBookSql = "select * from tb_book_info b LEFT JOIN tb_student u on b.update=u.id;";
    private final String insertBookSql = "insert into tb_book_info  values(?,?,?,?,?,?,?,?,?,?,?,?);";

    private final String updateBookSql = "update tb_book_info set id=?,name=?,author=?,publish=?,ISBN=?,introduction=?,language=?,price=?,pub_date=?,class_id=?,number=? where id = ?;";
    private final String deleteBookSql = "delete from tb_book_info where id=?;";
    private Connection conn = null;
    private PreparedStatement ps = null;
    private ResultSet rs = null;
    //定义用户对象并初始化，存储查询结果
    private Book bookSelect = null;
    private int result;
    private List<Book> allBook;

    private PreparedStatement createConn(String sql) {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            //获取连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/library?&useSSL=false&useUnicode=true&&characterEncoding=utf8", "root", "0714");
            //创建SQL语句
            ps = conn.prepareStatement(sql);
            //执行SQL语句
            return ps;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return ps;
    }

    private void closeConn() {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            if (ps != null) {
                ps.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    @Override
    public int addBook(Book book) {
        try {
            ps = createConn(insertBookSql);
            ps.setLong(1, book.getId());
            ps.setString(2, book.getName());
            ps.setString(3, book.getAuthor());
            ps.setString(4, book.getPublish());
            ps.setLong(5, book.getISBN());
            ps.setString(6, book.getIntroduction());
            ps.setString(7, book.getLanguage());
            ps.setDouble(8, book.getPrice());
            ps.setDate(9, new Date(book.getPubDate().getTime()));
            ps.setString(10, book.getClassId());
            ps.setInt(11, book.getNumber());
            ps.setLong(12, book.getUpdateUser());
            result = ps.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("Dao添加失败");
            return -1;
        } finally {
            //关闭资源
            closeConn();
        }
        return result;
    }

    @Override
    public int deleteBook(Long id) {
        try {
            ps = createConn(deleteBookSql);
            ps.setLong(1, id);
            result = ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("Dao删除失败");
            return -1;
        } finally {
            //关闭资源
            closeConn();
        }
        return result;
    }

    @Override
    public Book selectBook(Long id) {
        try {
            System.out.println(id);
            ps = createConn(selectBookSql);
            ps.setLong(1, id);
            rs = ps.executeQuery();
            while (rs.next()) {
                bookSelect = new Book(rs.getLong(1));
                bookSelect.setName(rs.getString(2));
                bookSelect.setAuthor(rs.getString(3));
                bookSelect.setPublish(rs.getString(4));
                bookSelect.setISBN(rs.getLong(5));
                bookSelect.setIntroduction(rs.getString(6));
                bookSelect.setLanguage(rs.getString(7));
                bookSelect.setPrice(rs.getDouble(8));
                bookSelect.setPubDate(rs.getDate(9));
                bookSelect.setClassId(rs.getString(10));
                bookSelect.setNumber(rs.getInt(11));
                bookSelect.setUpdateName(rs.getString(14));
                if (rs.getString(14)==null){
                    bookSelect.setUpdateName("未知");
                }
                System.out.println("Dao查找成功");
            }
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("Dao查找失败");
            return new Book(-1);
        } finally {
            //关闭资源
            closeConn();
        }
        return bookSelect;
    }

    @Override
    public List<Book> selectAllBook() {
        try {
            allBook = new ArrayList<Book>();
            ps = createConn(selectAllBookSql);
            rs = ps.executeQuery();
            while (rs.next()) {
                bookSelect = new Book();
                bookSelect.setId(rs.getLong(1));
                bookSelect.setName(rs.getString(2));
                bookSelect.setAuthor(rs.getString(3));
                bookSelect.setPublish(rs.getString(4));
                bookSelect.setISBN(rs.getLong(5));
                bookSelect.setIntroduction(rs.getString(6));
                bookSelect.setLanguage(rs.getString(7));
                bookSelect.setPrice(rs.getDouble(8));
                bookSelect.setPubDate(rs.getDate(9));
                bookSelect.setClassId(rs.getString(10));
                bookSelect.setNumber(rs.getInt(11));
                bookSelect.setUpdateName(rs.getString(14));
                if (rs.getString(14)==null){
                    bookSelect.setUpdateName("未知");
                }
                allBook.add(bookSelect);
            }
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("Dao查找失败");
            return null;
        } finally {
            //关闭资源
            closeConn();
        }
        return allBook;

    }

    @Override
    public int updateBook(Book book) {
        try {
            ps = createConn(updateBookSql);
            ps.setLong(1, book.getId());
            System.out.println(book.getId());
            ps.setString(2, book.getName());
            ps.setString(3, book.getAuthor());
            ps.setString(4, book.getPublish());
            ps.setLong(5, book.getISBN());
            ps.setString(6, book.getIntroduction());
            ps.setString(7, book.getLanguage());
            ps.setDouble(8, book.getPrice());
            ps.setDate(9, new Date(book.getPubDate().getTime()));
            ps.setString(10, book.getClassId());
            ps.setInt(11, book.getNumber());
            ps.setLong(12, book.getId());
            System.out.println(ps);
            result = ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("Dao修改失败");
            return -1;
        } finally {
            //关闭资源
            closeConn();
        }
        return result;
    }
}
